using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using Microsoft.Extensions.Primitives;

namespace QQZiFramework
{
    /// <summary>
    /// where条件项
    /// </summary>
    public abstract class IWhereItem
    {
        /// <summary>
        /// where拼接模板，正常是空，就是and或者or拼接${whereItem}，可以自定义模板，比如 exists(select 1 from tableB where ${whereItem} )
        /// 将解析后该项的where语句，替换到${whereItem},以实现exists里面多条件
        /// </summary>
        public string ConcatTemplate { get; set; }

        /// <summary>
        /// 解析成where条件，返回语句和参数
        /// </summary>
        /// <param name="context"></param>
        /// <param name="itemName">条件名，动态类的属性，或者QueryParam的where条件项</param>
        /// <param name="dbFieldName">数据库字段名</param>
        /// <param name="parameters">返回参数</param>
        /// <returns></returns>

        public abstract string ParseSql(DBContext context, string itemName, string dbFieldName, out System.Data.IDbDataParameter[] parameters);

    }


    public class WhereItem_Eq : WhereItem_Custom
    {
        public WhereItem_Eq(object value)
        {
            if (value == null) return;
            object[] expValues = null;
            if (value is string)
            {
                //以|分隔的数组
                expValues = ((string)value).Split('|');
            }
            else expValues = new object[] { value };
            base.Value = new WhereItem_Custom_Value()
            {
                exp = "${field} = {0}",
                values = expValues
            };

        }
    }

    public class WhereItem_NotEq : IWhereItem
    {
        object Value;
        public WhereItem_NotEq(object value)
        {
            if (value == null) return;
            this.Value = value;
        }


        public override string ParseSql(DBContext context, string itemName, string dbFieldName, out IDbDataParameter[] parameters)
        {
            if (this.Value is string) this.Value = context.FormatFromSource(this.Value.ToString().Replace("'", ""));
            parameters = new IDbDataParameter[] { context.CreateParameter(itemName.Replace(".", "_"), this.Value) };
            return dbFieldName + " <> " + parameters[0].ParameterName;
        }
    }

    public class WhereItem_In : IWhereItem
    {
        object[] Value;
        public WhereItem_In(object[] values)
        {
            if (values == null) return;
            for (int i = 0; i < values.Length; i++)
            {
                object obj = values[i];
                if (obj != null && obj is string)
                {
                    string str = (string)obj;
                    if (str.IndexOf("'") > -1) throw new Exception("in集合内不能再含有有单引号");
                    values[i] = "'" + str + "'";
                }
            }
            this.Value = values;
        }

        public override string ParseSql(DBContext context, string itemName, string dbFieldName, out IDbDataParameter[] parameters)
        {
            parameters = null;
            string str = string.Join(',', Value);
            return dbFieldName + " in (" + str + ") ";
        }
    }

    public class WhereItem_NotIn : IWhereItem
    {
        object[] Value;
        public WhereItem_NotIn(object[] values)
        {
            if (values == null) return;
            foreach (object obj in values)
            {
                if (obj != null && obj is string)
                {
                    string str = (string)obj;
                    if (str.IndexOf("'") > -1) throw new Exception("in集合内不能再含有有单引号");
                    str = "'" + str + "'";
                }
            }
            this.Value = values;
        }


        public override string ParseSql(DBContext context, string itemName, string dbFieldName, out IDbDataParameter[] parameters)
        {
            parameters = null;
            string str = string.Join(',', Value);
            return dbFieldName + " not in (" + str + ") ";
        }
    }

    public class WhereItem_Like : IWhereItem
    {
        string Value;
        public WhereItem_Like(string value)
        {
            if (value == null) return;
            this.Value = value.Replace("'", "");
        }


        public override string ParseSql(DBContext context, string itemName, string dbFieldName, out IDbDataParameter[] parameters)
        {
            parameters = null;
            this.Value = context.FormatFromSource(this.Value);
            parameters = new IDbDataParameter[] { context.CreateParameter(itemName, Value) };            
            return dbFieldName + " like " +context.GetDBAccess().ConcatSql("'%'",parameters[0].ParameterName,"'%'");
            // string concatFun = context.GetDBAccess().ConcatFunction();
            // string concatSymbol = context.GetDBAccess().Concat();
            // if (concatFun == null)
            // {
            //     return dbFieldName + " like '%'"+ + parameters[0].ParameterName + "+'%'";
            // }
            // else
            // {
            //     return dbFieldName + " like " + concatFun + "('%'," + parameters[0].ParameterName + ",'%')";
            // }
        }



    }

    public class WhereItem_StartWith : IWhereItem
    {
        string Value;
        public WhereItem_StartWith(string value)
        {
            if (value == null) return;
            this.Value = value.Replace("'", "");
        }


        public override string ParseSql(DBContext context, string itemName, string dbFieldName, out IDbDataParameter[] parameters)
        {
            this.Value = context.FormatFromSource(this.Value);
            parameters = new IDbDataParameter[] { context.CreateParameter(itemName, Value) };            
            return dbFieldName + " like " +context.GetDBAccess().ConcatSql(parameters[0].ParameterName,"'%'");
        }

    }

    public class WhereItem_Between : IWhereItem
    {
        object start, end;
        public WhereItem_Between(object start, object end)
        {
            if (start != null) this.start = start;
            if (end != null) this.end = end;
        }


        public override string ParseSql(DBContext context, string itemName, string dbFieldName, out IDbDataParameter[] parameters)
        {
            if (this.start != null && this.start is string) this.start = context.FormatFromSource(((string)this.start).Replace("'", ""));
            if (this.end != null && this.end is string) this.end = context.FormatFromSource(((string)this.end).Replace("'", ""));
            parameters = new IDbDataParameter[]{
                context.CreateParameter(itemName.Replace(".", "_")+"1",this.start),
                context.CreateParameter(itemName.Replace(".", "_")+"2",this.end)
            };

            if (start != null && end != null) return string.Format("{0} between {1} and {2}", dbFieldName, parameters[0].ParameterName, parameters[1].ParameterName);
            if (end == null && start != null) return string.Format("{0} >= {1} ", dbFieldName, parameters[0].ParameterName);
            if (start == null && end != null) return string.Format("{0} <= {1} ", dbFieldName, parameters[1].ParameterName);
            throw new Exception("between条件的start和end不能都为空");
        }
    }

    public class WhereItem_GroupBy : IWhereItem
    {

        public WhereItem_GroupBy()
        {

        }

        public override string ParseSql(DBContext context, string itemName, string dbFieldName, out IDbDataParameter[] parameters)
        {
            parameters = null;
            return "group by " + dbFieldName;
        }
    }

    public class WhereItem_Custom : IWhereItem
    {
        public static WhereItem_Custom_Value Create(string exp, params object[] values)
        {
            int pos = exp.IndexOf("$", 0, 5);//是否包含${field}或者$field
            if (pos == -1) exp = "${field} " + exp;
            return new WhereItem_Custom_Value() { exp = exp, values = values };
        }
        public WhereItem_Custom_Value Value { get; set; }

        public WhereItem_Custom() { }
        public WhereItem_Custom(string exp, params object[] values)
        {

            Value = new WhereItem_Custom_Value { exp = exp, values = values };
        }
        public bool IsGroupBy = false;
        bool hasPermissionConfig = false;
        // static System.Text.RegularExpressions.Regex likeRegex = new System.Text.RegularExpressions.Regex(@"like\s+'%\{0}%'");

        static ConcurrentDictionary<string, ConfigArrWhereExpCacheDataInfo> configArrWhereExpMappingCache = new ConcurrentDictionary<string, ConfigArrWhereExpCacheDataInfo>();
        static ConcurrentDictionary<string, CacheDataInfo> parseSqlCache = new ConcurrentDictionary<string, CacheDataInfo>();

        static System.Text.RegularExpressions.Regex sqlInExpressRegex = new System.Text.RegularExpressions.Regex(@"\b(?:in)\s*\(\s*(?:\{\s*\d+\s*\})\s*\)");

        static System.Text.RegularExpressions.Regex formatParameterExpressRegex = new System.Text.RegularExpressions.Regex(@"{(\d)}");
        //无需清理，因为都是根据configArrWhereExp等表达式缓存的，如果配置或传参变量，那表达式自然不同
        // public static void clearCache() 
        // {
        //     configArrWhereExpMappingCache.Clear();
        //     parseSqlCache.Clear();
        // }

        /// <summary>
        /// permission文件有where表达式的
        /// </summary>
        /// <param name="context"></param>
        /// <param name="configArrWhereExp">配置的自定义where表达式</param>       
        public void SetWhenPermissionHasConfig(DBContext context, string configArrWhereExp)
        {
            if (configArrWhereExp == null) return;
            hasPermissionConfig = true;
            ConfigArrWhereExpCacheDataInfo cacheValue;
            //使用缓存
            if (configArrWhereExpMappingCache.TryGetValue(configArrWhereExp, out cacheValue))
            {
                this.Value.exp = cacheValue.exp;
                this.IsGroupBy = cacheValue.isGroupBy;
                return;
            }
            //是否是groupby
            if (configArrWhereExp.IndexOf("group by", 0, configArrWhereExp.Length > 20 ? 20 : configArrWhereExp.Length, StringComparison.OrdinalIgnoreCase) > -1)
            {
                IsGroupBy = true;
            }
            else
            {
                //是否是like，兼容permission里面直接 like '%{0}%'的写法  
                string replaceValue=" like " +context.GetDBAccess().ConcatSql("'%'","{0}","'%'");
                // string concatFun = context.GetDBAccess().ConcatFunction();
                // string concatSymbol = context.GetDBAccess().Concat();
                // if (concatFun == null)
                // {
                //     replaceValue = "like '%'" + concatSymbol + "{0}" + concatSymbol + "'%'";
                // }
                // else
                // {
                //     replaceValue = "like " + concatFun + "('%',{0},'%')";
                // }
                configArrWhereExp = System.Text.RegularExpressions.Regex.Replace(configArrWhereExp, @"like\s+N?'%\{0\}%'", replaceValue, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                //configArrWhereExp = match.Result(replaceValue);




            }
            Value.exp = configArrWhereExp;
            configArrWhereExpMappingCache[configArrWhereExp] = new ConfigArrWhereExpCacheDataInfo { exp = Value.exp, isGroupBy = IsGroupBy };
        }



        public override string ParseSql(DBContext context, string itemName, string dbFieldName, out IDbDataParameter[] parameters)
        {
            string cacheKey = dbFieldName + ":" + Value.exp;
            CacheDataInfo cacheValue;
            if (!parseSqlCache.TryGetValue(cacheKey, out cacheValue))
            {
                //添加缓存
                cacheValue = new CacheDataInfo();
                cacheValue.sqlExp = Value.exp.Replace("${field}", dbFieldName).Replace("$field", dbFieldName);

                if (Value.values != null)
                {
                    int parameterCountTemp = Value.values.Length;
                    // List<string> parameterNames = new List<string>(parameterCountTemp);
                    List<SqlParameterNameAndIndex> parameterNames_no_symbol = new List<SqlParameterNameAndIndex>(parameterCountTemp);//没有符号的参数列表
                    // for (int i = 0; i < parameterCountTemp; i++)
                    // {
                    //     //这里的参数需要带符号，比如sqlserver的@，所以用CreateParameter
                    //     string tempName = itemName.Replace(".", "_") + (i + 1);
                    //     string parameterName =context.CreateParameter(tempName,Value.values[i]).ParameterName;
                    //     parameterNames.Add(parameterName);
                    //     parameterNames_no_symbol.Add(tempName);
                    // }
                    cacheValue.sqlExp = cacheValue.sqlExp.Replace("'{", "{").Replace("}'", "}");//替换${field}，去除'{0}'的单个引号

                    //判断in表达式，因为in不能用sql参数
                    var matchGroup = sqlInExpressRegex.Matches(cacheValue.sqlExp);
                    if (matchGroup != null && matchGroup.Count > 0)
                    {
                        cacheValue.IN_ParamtersMap = new Dictionary<string, int>();
                        int i = 0;
                        foreach (Match match in matchGroup)
                        {
                            //替换掉in表达式{0}等
                            Match m = formatParameterExpressRegex.Match(match.Value);
                            var formatName = "__inValue__" + i;
                            cacheValue.sqlExp = cacheValue.sqlExp.Replace(m.Value, formatName);
                            var formatIndex = Convert.ToInt32(m.Value.Substring(1, m.Value.Length - 2));//{formatIndex}                            
                            cacheValue.IN_ParamtersMap.Add(formatName, formatIndex);//in ({0}) 的 , 记录索引
                            i++;
                        }
                    }
                    //替换{0}等为sql参数名
                    //不使用string.form，{loginUserId}会报错
                    // cacheValue.sqlExp = string.Format(cacheValue.sqlExp, sqlParameters);
                    int seq = 1;
                    bool parameterMultiplexing = context.GetDBAccess().ParameterMultiplexing;
                    cacheValue.sqlExp = System.Text.RegularExpressions.Regex.Replace(cacheValue.sqlExp, @"\{(\d+)\}", m =>
                    {
                        int index = Convert.ToInt32(m.Groups[1].Value);
                        // parameterNames.Add(parameterName);
                        if (parameterMultiplexing)
                        {
                            var existsParameter = parameterNames_no_symbol.Find(x => x.index == index);
                            if (existsParameter != null) return existsParameter.nameWithSymbol;
                        }
                        string tempName = itemName.Replace(".", "_") + seq;
                        seq++;
                        string parameterName = context.CreateParameter(tempName, Value.values[index]).ParameterName;
                        parameterNames_no_symbol.Add(new SqlParameterNameAndIndex(tempName, parameterName, index));
                        if (index < parameterCountTemp) return parameterName;
                        else return null;
                    });
                    //in的，移除cacheValue.sqlParameterNames参数
                    // if (cacheValue.IN_ParamtersMap != null)
                    // {
                    //     List<int> removedIndexs = cacheValue.IN_ParamtersMap.Values.ToList();
                    //     removedIndexs.Sort((a, b) => b.CompareTo(a));//降序排序
                    //     foreach (var index in removedIndexs)
                    //     {
                    //         // parameterNames.RemoveAt(index);
                    //         parameterNames_no_symbol.RemoveAt(index);
                    //     }
                    // }
                    cacheValue.sqlParameterNames = parameterNames_no_symbol.ToArray();
                    parameterNames_no_symbol = null;
                    // parameterNames = null;
                }
                parseSqlCache[cacheKey] = cacheValue;
            }

            parameters = null;
            if (Value.values != null)
            {
                for (int i = 0; i < Value.values.Length; i++)
                {
                    if (Value.values[i] != null && Value.values[i] is string)
                    {
                        //替换{loginUserId}等FormatSource
                        Value.values[i] = context.FormatFromSource((string)Value.values[i]);
                    }
                }
            }
            //组织sql参数（不包括permission里面配置的in语句参数）
            List<IDbDataParameter> parametersList = null;
            if (cacheValue.sqlParameterNames != null)
            {
                parametersList = new List<IDbDataParameter>(cacheValue.sqlParameterNames.Length);
                for (int i = 0; i < cacheValue.sqlParameterNames.Length; i++)
                {
                    var parameterCache = cacheValue.sqlParameterNames[i];
                    parametersList.Add(context.GetDBAccess().CreateParameter(parameterCache.name, Value.values[parameterCache.index]));
                }
            }
            //替换{loginUserId}等FormatSource
            var sqlExp = context.FormatFromSource(cacheValue.sqlExp);
            //添加in语句的sql参数
            if (cacheValue.IN_ParamtersMap != null)
            {
                int i = 1;
                foreach (var item in cacheValue.IN_ParamtersMap)
                {
                    var formatIndex = item.Value;
                    var parameterName = item.Key;
                    var inValues = Value.values[formatIndex] as string;
                    if (inValues == null || inValues.Length == 0) throw new Exception("in语句的值不能为空");
                    var replacedParameters = "";
                    foreach (var str in inValues.Split(','))
                    {
                        var parameter = context.GetDBAccess().CreateParameter(itemName + i, str);
                        replacedParameters += parameter.ParameterName + ",";
                        parametersList.Add(parameter);
                        i++;
                    }
                    sqlExp = sqlExp.Replace(parameterName, replacedParameters.Substring(0, replacedParameters.Length - 1));
                }
            }
            if (parametersList != null) parameters = parametersList.ToArray();
            return sqlExp;
        }




        public class ConfigArrWhereExpCacheDataInfo
        {
            public string exp;
            // public bool hasStringFormat;
            internal bool isGroupBy;
        }

        public class CacheDataInfo
        {
            public string sqlExp;
            /// <summary>
            /// sql参数名集合，不包括前缀符号，比如@
            /// </summary>
            public SqlParameterNameAndIndex[] sqlParameterNames;

            // internal bool hasStringFormat;
            // internal MatchCollection inExpressMatchs;

            // /// <summary>
            // /// in表达式的位置，通常是1个， 也就是"in ({0})"里的0
            // /// </summary>
            // /// <value></value>
            // public int[] InSqlFormatIndexs { get; internal set; }

            public Dictionary<string, int> IN_ParamtersMap;
        }

        public class SqlParameterNameAndIndex
        {
            /// <summary>
            /// 无符号的参数名
            /// </summary>
            public string name;

            /// <summary>
            /// 有符号的参数名
            /// </summary>
            public string nameWithSymbol;

            /// <summary>
            /// 索引 ，语句中{0}..{1}这样的值
            /// </summary> 
            public int index;

            public SqlParameterNameAndIndex(string name, string nameWithSymbol, int index)
            {
                this.name = name;
                this.index = index;
                this.nameWithSymbol = nameWithSymbol;
            }
        }

        public class WhereItem_Custom_Value
        {
            public string exp { get; set; }
            public object[] values { get; set; }


        }
    }

}